package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import dto.Cdd;

public class ManterCddDAO {
	
	
private Connection conexao = FabricaConexoesBanco.getInstancia().getConexao();

	
	public Iterator<Cdd> consultarCddPorNome(String nome) {
		String sql = "SELECT * FROM cdd where nome like ? order by nome";
		
		List<Cdd> list = new ArrayList<Cdd>();

		try {
			PreparedStatement pstmt = conexao.prepareStatement(sql);
			pstmt.setString(1, "%"+nome+"%");

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {

				Cdd cdd = new Cdd();
				cdd.setIdCdd(rs.getInt("id_cdd"));
				cdd.setNomeCdd(rs.getString("nome"));
				
				list.add(cdd);

			}
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return list.iterator();
	}
	
	
	
	public Cdd consultarCddPorId(int id) {
		
		String sql = "SELECT * FROM cdd where id_cdd = ?";

		Cdd cdd = new Cdd();
		try {
			PreparedStatement pstmt = conexao.prepareStatement(sql);
			pstmt.setInt(1, id);
			
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				cdd.setIdCdd(rs.getInt("id_cdd"));
				cdd.setNomeCdd(rs.getString("nome"));

			}
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return cdd;
	}
	
	
	
	
	
	
	public void alterar(Cdd cdd) {
		String sql = "UPDATE cdd SET nome = ? WHERE id_cdd = ?";

		try {
			PreparedStatement pstmt = conexao.prepareStatement(sql);

			pstmt.setString(1, cdd.getNomeCdd());
			pstmt.setInt(2, cdd.getIdCdd());

			pstmt.execute();

			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
	
	
	public void excluirCdd(int id) {
		String sql = "DELETE FROM cdd WHERE id_cdd = ?";

		try {
			PreparedStatement pstmt = conexao.prepareStatement(sql);

			pstmt.setInt(1,id);

			pstmt.executeUpdate();

			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	public void incluir(Cdd cdd) {
		String sql = "INSERT INTO cdd (nome) values(?)";

		try {
			PreparedStatement pstmt = conexao.prepareStatement(sql);

			pstmt.setString(1, cdd.getNomeCdd());

			pstmt.execute();

			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	public Iterator<Cdd> consultarTodos() {
		String sql = "SELECT * FROM cdd  order by id_cdd";
		List<Cdd> list = new ArrayList<Cdd>();

		try {
			PreparedStatement pstmt = conexao.prepareStatement(sql);

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {

				Cdd cdd = new Cdd();
				cdd.setIdCdd(rs.getInt("id_cdd"));
				cdd.setNomeCdd(rs.getString("nome"));
				
				list.add(cdd);

			}
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return list.iterator();
	}

}
